﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DreamBuilder.Common;

namespace Web.SystemConfig.DataAccess
{
    public class DALAuth_Org_User : DataAccessBase
    {
        internal DataTable GetAllOrgData()
        {
            string strSql = "";
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                strSql = @"select t.*, nvl(usercount,0) as usercount
                        from p_auth_org t
                        left outer join (Select U.ORG_ID,
                      count(U.ORG_USER_ID) as usercount
                      from P_Auth_ORG_User U
                      group by U.ORG_ID) t1 on t.org_id = t1.org_id ";
            }
            else
            {
                strSql = "SELECT *,(select count(*) from dbo.P_Auth_ORG_User b where b.ORG_ID = P_Auth_ORG.ORG_ID) as usercount FROM P_Auth_ORG  ";

            }
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }

        internal DataTable GetUserInfo(string pkvalue)
        {
            string strSql = "SELECT t.*,c.code_desc as statusName FROM P_Auth_User t  left outer join P_BT_CodeTable c on CodeTableID = t.Status_ID  where User_ID= '" + pkvalue + "' ";
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }

        internal DataTable SelectRolesByOrgID(string OrgID)
        {
            string strSql = "";
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                if (string.IsNullOrEmpty(OrgID))
                {
                    strSql = " select P_Auth_Role.Role_ID,P_Auth_Role.Role_Name,0 as HasRole  from  P_Auth_Role";
                }
                else
                {
                    strSql = @" SELECT  
                          P_Auth_Role.Role_ID,  
                          P_Auth_Role.Role_Name,  
                          case when P_Auth_ORG_Role.ORG_ID is null then 0  
                          else 1 end as HasRole  
                         FROM  
                          P_Auth_Role  
                         left join  
                          P_Auth_ORG_Role  
                         on  
                          P_Auth_Role.Role_ID = P_Auth_ORG_Role.Role_ID and P_Auth_ORG_Role.ORG_ID = '" + OrgID + "'  ";
                }
            }
            else
            {
                if (string.IsNullOrEmpty(OrgID))
                {
                    strSql = " select P_Auth_Role.Role_ID,P_Auth_Role.Role_Name,cast(0 as bit) as HasRole  from  P_Auth_Role";
                }
                else
                {
                    strSql = @" SELECT  
                          P_Auth_Role.Role_ID,  
                          P_Auth_Role.Role_Name,  
                          case when P_Auth_ORG_Role.ORG_ID is null then cast(0 as bit)  
                          else cast(1 as bit) end as HasRole  
                         FROM  
                          P_Auth_Role  
                         left join  
                          P_Auth_ORG_Role  
                         on  
                          P_Auth_Role.Role_ID = P_Auth_ORG_Role.Role_ID and P_Auth_ORG_Role.ORG_ID = '" + OrgID + "'  ";
                }
            }

            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }

        internal void InsertUserRole(string OrgID, string roleID)
        {
            string sSql = " INSERT INTO P_Auth_ORG_Role(Role_ID,ORG_ID) VALUES ('" + roleID + "','" + OrgID + "' )";
            DBHelper.ExecuteSql(sSql);
        }

        internal void DeleteUserRole(string OrgID, string roleID)
        {
            string sSql = "DELETE FROM P_Auth_ORG_Role  where ORG_ID='" + OrgID + "' and Role_ID= '" + roleID + "'";
            DBHelper.ExecuteSql(sSql);
        }
        internal bool CheckUserDepartment(string Uesr_ID, string ORG_ID)
        {
            string strSql = "Select * From P_Auth_ORG_User Where User_ID = '" + Uesr_ID + "' and ORG_ID = '" + ORG_ID + "'";
            DataTable dtUserDepartment = DBHelper.ExecuteDataSet(strSql).Tables[0];
            if (dtUserDepartment != null && dtUserDepartment.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        internal void InsertOrgUserUpdateUser(DataRow dr)
        {
            string userID = dr["P_Auth_ORG_User.User_ID"].ToString();
            string OrgID = dr["P_Auth_ORG_User.ORG_ID"].ToString();
            DataTable dtUser = GetUserInfo(userID);
            //如果User表中OrgID没有值,则更新为新添加的OrgID
            if (dtUser.Rows[0]["ORG_ID"] == DBNull.Value || dtUser.Rows[0]["ORG_ID"].ToString().Trim().Length == 0)
            {
                UpdateUserOrg(userID, OrgID);
            }
        }
        internal void DeleteOrgUserUpdateUser(DataRow dr)
        {
            string userID = dr["P_Auth_ORG_User.User_ID"].ToString();
            string OrgID = dr["P_Auth_ORG_User.ORG_ID"].ToString();
            DataTable dtUser = GetUserInfo(userID);
            //如果User表中OrgID和新更新的OrgID相同
            if (dtUser.Rows[0]["ORG_ID"].ToString() == OrgID)
            {
                DataTable dtMoreOrg = GetMoreOrg(userID, OrgID);
                //如果除了删除的部门还有其他的部门，更新User的orgid为其他的部门
                if (dtMoreOrg.Rows.Count > 0)
                {
                    UpdateUserOrg(userID, dtMoreOrg.Rows[0]["ORG_ID"].ToString());
                }
                //没有其他部门，则清空该字段
                else
                {
                    UpdateUserOrgNull(userID);
                }
            }
        }

        private void UpdateUserOrgNull(string userID)
        {
            string sqlstr = " update P_Auth_User set ORG_ID = null where User_ID = '" + userID + "'";
            DBHelper.ExecuteSql(sqlstr);
        }

        /// <summary>
        /// 获取该用户这个orgid之外的其他的OrgID
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="OrgID"></param>
        /// <returns></returns>
        private DataTable GetMoreOrg(string userID, string OrgID)
        {
            string strsql = "select ORG_ID from P_Auth_ORG_User where User_ID = '" + userID + "' and ORG_ID != '" + OrgID + "' ";
            return DBHelper.ExecuteDataSet(strsql).Tables[0];
        }
        private void UpdateUserOrg(string userID, string OrgID)
        {
            string sqlstr = " update P_Auth_User set ORG_ID = '" + OrgID + "' where User_ID = '" + userID + "'";
            DBHelper.ExecuteSql(sqlstr);
        }
    }
}
